package com.example.ida;


import java.io.File;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class IdaDB extends SQLiteOpenHelper {
	
	final static int TABLE_PHOTO = 0 ;
	final static int TABLE_ANI = 1 ;
//	final static int TABLE_PRE = 2 ;
	
	
	Context context;
	String dbName;
	int dbVersion;
	SQLiteDatabase db;

	public IdaDB(Context context, String name, CursorFactory factory, int version)
	{
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
		this.context = context;
		this.dbName = name;
		this.dbVersion = version;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		this.db = db;
		String sql="";
		sql = "create table if not exists photo (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
		db.execSQL(sql);
		sql = "create table if not exists ani (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
		db.execSQL(sql);
//		sql = "create table if not exists pre (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
//		db.execSQL(sql);
//		else if(dbName.equals("grid"))
//		{
//			
//		}
		
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public boolean cameraInsert(String path, String fname, String baby, int table)
	{
		boolean flag = true;
		db = getWritableDatabase();
		String sql="";
		switch(table)
		{
		case TABLE_PHOTO:
			sql = "insert into photo(path, fname, baby) values(?,?,?)";
			break;
		case TABLE_ANI:
			sql = "insert into ani(path, fname, baby) values(?,?,?)";
			break;
//		case TABLE_PRE:
//			sql = "insert into pre(path, fname, baby) values(?,?,?)";
//			break;
		}
		
		String[] params = {path, fname, baby};
		try
		{
			db.beginTransaction();
			db.execSQL(sql, params);
			db.setTransactionSuccessful();
		}
		catch(Exception ex)
		{
			flag = false;
		}
		finally
		{
			db.endTransaction();
		}
		return flag;
	}
	
	public ArrayList<String> select(String baby, int table)
	{
		db = getReadableDatabase();
		
		ArrayList<String> al = new ArrayList<String>();
		String sql="";
		switch(table)
		{
		case TABLE_PHOTO:
			sql="select path, fname from photo where baby="+baby+" order by pno";
			break;
		case TABLE_ANI:
			sql="select path, fname from ani where baby="+baby+" order by pno";
			break;
//		case TABLE_PRE:
//			sql="select path, fname from pre where baby="+baby+" order by pno";
//			break;
		}
		
		Cursor c = db.rawQuery(sql, null);
//		Toast.makeText(context, baby, Toast.LENGTH_SHORT).show();
		while(c.moveToNext())
		{
			String fullpath = c.getString(0) + c.getString(1);
//			Toast.makeText(context, fullpath, Toast.LENGTH_SHORT).show();
			al.add(fullpath);
		}
		return al;
	}
	
	public String cameraItem(String fname)
	{
		String pno="";
		db = getReadableDatabase();
		Toast.makeText(context, fname, Toast.LENGTH_SHORT).show();
		String sql = "select pno from photo where fname='"+fname+"'";
		Cursor c = db.rawQuery(sql, null);
		while(c.moveToNext())
		{
			pno = String.valueOf(c.getInt(0));
		}
		
		return pno;
	}

	public boolean cameraTruncate()
	{
		boolean trun = true;
		db = getWritableDatabase();
		String sql = "drop table photo";
		db.execSQL(sql);
		sql = "create table photo (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
		db.execSQL(sql);
		sql = "drop table photo";
		db.execSQL(sql);
		sql = "create table ani (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
		db.execSQL(sql);
		sql = "drop table photo";
		db.execSQL(sql);
		sql = "create table pre (pno Integer primary key autoincrement, path varchar2(300), fname varchar2(30), baby Integer)";
		db.execSQL(sql);
		return trun;
	}
	
	public boolean cameraPhotoDel()
	{
		boolean del = true;
		db = getReadableDatabase();
		String sql = "select * from photo";
		Cursor c = db.rawQuery(sql, null);
		while(c.moveToNext())
		{
			int pno = c.getInt(0);
			String path = c.getString(1);
			String fname = c.getString(2);
			File f = new File(path+fname);
			del = f.delete();
		}
		return del;
	}
	public boolean photoDel(String pno)
	{
		boolean del = true;
		db = getWritableDatabase();
		String sql = "delete from photo where pno="+pno;
		db.execSQL(sql);
		sql = "delete from ani where pno="+pno;
		db.execSQL(sql);
//		sql = "delete from pre where pno="+pno;
//		db.execSQL(sql);
		return del;
	}
	
	public void testsel(String baby)
	{
		db = getReadableDatabase();
		
		String sql="select path, fname, baby from photo where baby="+baby;
		Cursor c = db.rawQuery(sql, null);
		Toast.makeText(context, "idb/"+baby, Toast.LENGTH_SHORT).show();
		while(c.moveToNext())
		{
			String fullpath = c.getString(0) + c.getString(1);
			Toast.makeText(context, fullpath+"/ dbaby = "+c.getString(2), Toast.LENGTH_SHORT).show();
		}
	}
}
